package weaver.tools.util;


import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Title: ecology-idea
 * @Company: 泛微软件
 * @author: weaver.Dusongsong
 * @version: 1.0
 * @CreateDate: 2022-06-16
 * @Description: 导出ExcelUtil
 * @ModifyLog:
 **/

public class ExcelUtil {
    /**
     * 生成Excel 并放到指定位置
     *
     * @param titlelist 标题名称list
     * @param zdlist    字段list
     * @param datalist  数据list
     * @return 是否正常生成
     * @throws IOException
     * @author: (titlelist 和 zdlist 顺序要一致, 要一一对应)
     */
    public static boolean createTaxInfoExcel(String sheetname, List<String> titlelist, List<String> zdlist, List<Map<String, Object>> datalist) throws IOException {
        boolean success = false;
        String filepath = "F:\\download\\";
        String filename = sheetname + ".xls";
        try {
            //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetname);
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            HSSFRow row0 = sheet.createRow(0);
            Cell cell = row0.createCell(0);
            cell.setCellValue(sheetname);
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titlelist.size() - 1));
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(1);
            // 添加表头
            for (int i = 0; i < titlelist.size(); i++) {
                cell = row1.createCell(i);
                cell.setCellValue(titlelist.get(i));
                cell.setCellStyle(cellStyle);
            }
            //添加表中内容
            String sum_yfje = "0.00";
            String sum_dkgs = "0.00";
            String sum_sfje = "0.00";
            for (int row = 0; row < datalist.size(); row++) {//数据行
                HSSFRow newrow = sheet.createRow(row + 2);//数据从第三行开始
                Map<String, Object> data = datalist.get(row); //获取该行的数据
                for (int col = 0; col < zdlist.size(); col++) {//列
                    cell = newrow.createCell(col);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue("");
                }
            }
            HSSFRow sunrow = sheet.createRow(datalist.size() + 2);
            for (int i = 0; i < titlelist.size(); i++) {
                cell = sunrow.createCell(i);
                if (i < 3) {
                    cell.setCellValue("合计:");
                } else if (i == 3) {
                    cell.setCellValue(sum_yfje);
                } else if (i == 4) {
                    cell.setCellValue(sum_dkgs);
                } else if (i == 5) {
                    cell.setCellValue(sum_sfje);
                }

                cell.setCellStyle(cellStyle);
            }
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 2, datalist.size() + 2, 0, 2));
            HSSFRow tailrow = sheet.createRow(datalist.size() + 4);
            tailrow.createCell(0);
            tailrow.createCell(1).setCellValue("审核:");
            tailrow.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 1, 2));
            tailrow.createCell(3);
            tailrow.createCell(4).setCellValue("制单:");
            tailrow.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 4, 5));

            //输出Excel文件1
            FileOutputStream output = new FileOutputStream(filepath + filename);
            wb.write(output);//写入磁盘
            output.close();
            success = true;
        } catch (Exception e) {
            success = false;
            e.printStackTrace();
        }
        return success;
    }

    /**
     * 生成Excel 并放到指定位置
     *
     * @param titlelist 标题名称list
     * @param zdlist    字段list
     * @param datalist  数据list
     * @return 是否正常生成
     * @throws IOException
     * @author: (titlelist 和 zdlist 顺序要一致, 要一一对应)
     */
    public static boolean createProvideExcel(String sheetname, List<String> titlelist, List<String> zdlist, List<Map<String, Object>> datalist) throws IOException {
        boolean success = false;
        String filepath = "F:\\download\\";
        String filename = sheetname + ".xls";
        try {
            //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetname);
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            HSSFRow row0 = sheet.createRow(0);
            Cell cell = row0.createCell(0);
            cell.setCellValue(sheetname);
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titlelist.size() - 1));
            HSSFRow row1 = sheet.createRow(1);
            cell = row1.createCell(0);
            cell.setCellValue("时间：2022年6月21日——2022年6月21日(1天)");
            cell.setCellStyle(cellStyle);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, titlelist.size() - 1));
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row2 = sheet.createRow(2);
            // 添加表头
            for (int i = 0; i < titlelist.size(); i++) {
                cell = row2.createCell(i);
                cell.setCellValue(titlelist.get(i));
                cell.setCellStyle(cellStyle);
            }
            //添加表中内容
            String sum_ffje = "0.00";
            for (int row = 0; row < datalist.size(); row++) {//数据行
                HSSFRow newrow = sheet.createRow(row + 3);//数据从第三行开始
                Map<String, Object> data = datalist.get(row); //获取该行的数据
                for (int col = 0; col < zdlist.size(); col++) {//列
                    cell = newrow.createCell(col);
                    cell.setCellStyle(cellStyle);
                    if (data != null && data.get(zdlist.get(col)) != null) {
                        cell.setCellValue(String.valueOf(data.get(zdlist.get(col))));
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
            HSSFRow sunrow = sheet.createRow(datalist.size() + 3);
            for (int i = 0; i < titlelist.size(); i++) {
                cell = sunrow.createCell(i);
                if (i < 4) {
                    cell.setCellValue("合计:");
                } else if (i == 12) {
                    cell.setCellValue(sum_ffje);
                }

                cell.setCellStyle(cellStyle);
            }
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 3, datalist.size() + 3, 0, 3));
            HSSFRow tailrow = sheet.createRow(datalist.size() + 4);
            tailrow.createCell(0);
            tailrow.createCell(1).setCellValue("所长:");
            tailrow.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 1, 2));
            tailrow.createCell(3);
            tailrow.createCell(4).setCellValue("主管所领导:");
            tailrow.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 4, 5));
            tailrow.createCell(6);
            tailrow.createCell(7).setCellValue("部门负责人:");
            tailrow.createCell(8);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 7, 8));
            tailrow.createCell(9);
            tailrow.createCell(10).setCellValue("经办人:");
            tailrow.createCell(11);
            sheet.addMergedRegion(new CellRangeAddress(datalist.size() + 4, datalist.size() + 4, 10, 11));

            //输出Excel文件1
            FileOutputStream output = new FileOutputStream(filepath + filename);
            wb.write(output);//写入磁盘
            output.close();
            success = true;
        } catch (Exception e) {
            success = false;
            e.printStackTrace();
        }
        return success;
    }

    public static void generateTaxInfoData(List<Map<String, Object>> datalist) throws Exception {
        String sheetname = "专家咨询费代扣个税信息表";
        List<String> titlelist = new ArrayList<>();
        titlelist.add("序号");
        titlelist.add("姓名");
        titlelist.add("身份证号");
        titlelist.add("应发金额");
        titlelist.add("代扣个税");
        titlelist.add("实发金额");
        List<String> zdlist = new ArrayList<>();
        zdlist.add("xh");
        zdlist.add("xm");
        zdlist.add("sfzh");
        zdlist.add("yfje");
        zdlist.add("dkgs");
        zdlist.add("sfje");
        createTaxInfoExcel(sheetname, titlelist, zdlist, datalist);
    }

    public static void generateProvideData(List<Map<String, Object>> datalist) throws Exception {
        String sheetname = "会议专家咨询费发放表";
        List<String> titlelist = new ArrayList<>();
        titlelist.add("序号");
        titlelist.add("会议名称");
        titlelist.add("姓名");
        titlelist.add("单位");
        titlelist.add("身份证号");
        titlelist.add("职称");
        titlelist.add("手机号");
        titlelist.add("收款账户开户行");
        titlelist.add("收款账户地址");
        titlelist.add("收款账户");
        titlelist.add("收款账户地区码");
        titlelist.add("收款账户全称");
        titlelist.add("发放金额");
        List<String> zdlist = new ArrayList<>();
        zdlist.add("xh");
        zdlist.add("hymc");
        zdlist.add("xm");
        zdlist.add("dw");
        zdlist.add("sfzh");
        zdlist.add("zc");
        zdlist.add("sjh");
        zdlist.add("skzhkhh");
        zdlist.add("skzhdz");
        zdlist.add("skzh");
        zdlist.add("skzhdqm");
        zdlist.add("skzhqc");
        zdlist.add("ffje");
        createProvideExcel(sheetname, titlelist, zdlist, datalist);
    }

    public static void main(String[] args) throws Exception {
        List<Map<String, Object>> datalist = new ArrayList<>();
        Map<String, Object> data = new HashMap<>();
        data.put("xh", "1");
        data.put("xm", "张三");
        data.put("sfzh", "32205413431823115");
        data.put("yfje", "1050.20");
        data.put("dkgs", "100.52");
        data.put("sfje", "980.00");
        datalist.add(data);
        generateTaxInfoData(datalist);
        List<Map<String, Object>> datalist1 = new ArrayList<>();
        Map<String, Object> data1 = new HashMap<>();
        data1.put("xh", "1");
        data1.put("hymc", "测试会议");
        data1.put("xm", "李四");
        data1.put("dw", "测试单位");
        data1.put("sfzh", "858885858952522");
        data1.put("zc", "高级专家");
        data1.put("sjh", "1524858558");
        data1.put("skzhkhh", "1524858558");
        data1.put("skzhdz", "中国工商银行支行");
        data1.put("skzh", "15248585582321321");
        data1.put("skzhdqm", "1524");
        data1.put("skzhqc", "中国工商银行支行");
        data1.put("ffje", "100.00");
        datalist1.add(data1);
        generateProvideData(datalist1);

    }

}
